package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.datacenter.vo.NoticeDataReportVO;
import com.dy.yunying.api.req.NoticeDataReq;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

/**
 * @author yuwenfeng
 * @description: 公告数据
 * @date 2022/3/8 11:09
 */
@Component
@Slf4j
public class NoticeDataDao {

	@Resource(name = "hbdataclickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Autowired
	private YunYingProperties yunYingProperties;

	public Long countNoticeDataTotal(NoticeDataReq req) {
		StringBuilder countSql = new StringBuilder();
		StringBuilder sql = getSql(req);
		countSql.append("SELECT COUNT(1) FROM (").append(sql).append(")");
		log.info("通知公告数据count.sql:[{}]", countSql.toString());
		return clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<NoticeDataReportVO> selectNoticeDataReport(NoticeDataReq req) {
		List<NoticeDataReportVO> data = new ArrayList<>();
		StringBuilder sql = getSql(req);
		if (StringUtils.isNotBlank(req.getSortColumn()) && StringUtils.isNotBlank(req.getSortType())) {
			sql.append(" ORDER BY\n");
			sql.append(" ").append(req.getSortColumn()).append(" ").append(req.getSortType()).append('\n');
		} else {
			sql.append(" ORDER BY \n");
			sql.append(req.getPeriod() + " DESC \n");
		}
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		log.info("通知公告数据.sql:[{}]", sql.toString());
		SqlRowSet rs = clickhouseTemplate.queryForRowSet(sql.toString());
		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());
		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			NoticeDataReportVO item = ObjByRow(rs, columns, "period", req);
			data.add(item);
		}
		return data;
	}

	private NoticeDataReportVO ObjByRow(SqlRowSet rs, List<String> columns, String type, NoticeDataReq req) {
		NoticeDataReportVO vo = new NoticeDataReportVO();
		if ("period".equals(type)) {
			vo.setPeriod(rs.getString(req.getPeriod()));
		}
		//主渠道
		if (columns.contains("parentchl") && StringUtils.isNotBlank(rs.getString("parentchl"))) {
			vo.setParentchl(rs.getString("parentchl"));
		} else {
			vo.setParentchl("-");
		}

		//子渠道
		if (columns.contains("chl") && StringUtils.isNotBlank(rs.getString("chl"))) {
			vo.setChl(rs.getString("chl"));
		} else {
			vo.setChl("-");
		}

		//父游戏 id
		if (columns.contains("pgid")) {
			vo.setPgid(rs.getLong("pgid"));
		}

		//子游戏 id
		if (columns.contains("gameid")) {
			vo.setGameid(rs.getLong("gameid"));
		}

		if (columns.contains("notice_id")) {
			vo.setNoticeId(rs.getLong("notice_id"));
		}

		if (columns.contains("touchUserSum")) {
			vo.setTouchUserSum(rs.getBigDecimal("touchUserSum"));
		}

		if (columns.contains("clickNumberSum")) {
			vo.setClickNumberSum(rs.getBigDecimal("clickNumberSum"));
		}

		if (columns.contains("showNumberSum")) {
			vo.setShowNumberSum(rs.getBigDecimal("showNumberSum"));
		}

		if (columns.contains("clickUserSum")) {
			vo.setClickUserSum(rs.getBigDecimal("clickUserSum"));
		}

		if (columns.contains("touchRatio")) {
			vo.setTouchRatio(rs.getBigDecimal("touchRatio"));
		}

		if (columns.contains("clickRatio")) {
			vo.setClickRatio(rs.getBigDecimal("clickRatio"));
		}
		return vo;
	}

	private StringBuilder getSql(NoticeDataReq req) {
		StringBuilder sql = new StringBuilder();

		sql.append("  SELECT \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" touchUserSum, ");
		sql.append(" clickNumberSum, ");
		sql.append(" showNumberSum, ");
		sql.append(" clickUserSum, ");
		sql.append(" round(IF(toInt64(touchUserSum) > 0,divide(clickUserSum * 100.00,touchUserSum),0),2) AS touchRatio, ");
		sql.append(" round(IF(toInt64(showNumberSum) > 0,divide(clickNumberSum * 100.00,showNumberSum),0),2) AS clickRatio ");
		sql.append(" from ( \n");
		sql.append("  SELECT \n  ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" COUNT( 1 ) AS showNumberSum, ");
		sql.append(" COUNT( DISTINCT IF (username == '', roleid, username) ) AS touchUserSum ");
		sql.append(" from \n");
		sql.append("  " + yunYingProperties.getNoticeDxView() + "  ");
		sql.append(" WHERE ");
		sql.append(" dx_event_name = 'page_view' ");
		sql.append(this.selectCondition(req, " "));
		sql.append(" GROUP BY ");
		sql.append(this.getGroupColumnSql(req));
		sql.append(" ) viewData ");
		sql.append(" FULL JOIN ( ");
		sql.append(" SELECT \n ");
		sql.append(this.getQueryColumnSql(req));
		sql.append(" COUNT( 1 ) AS clickNumberSum, ");
		sql.append(" COUNT( DISTINCT IF (username == '', roleid, username) ) AS clickUserSum ");
		sql.append(" from \n");
		sql.append("  " + yunYingProperties.getNoticeDxView() + "  ");
		sql.append(" WHERE ");
		sql.append(" dx_event_name = 'module_click' ");
		sql.append(this.selectCondition(req, " "));
		sql.append(" GROUP BY ");
		sql.append(this.getGroupColumnSql(req));
		sql.append(" ) clickData ");
		sql.append(" USING ( ");
		sql.append(this.getGroupColumnSql(req));
		sql.append("  ) \n");

		return sql;
	}

	//类别参数
	private StringBuilder getQueryColumnSql(NoticeDataReq req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			queryColumnSql.append(queryColumn);
			queryColumnSql.append(",");
		}
		sql.append(req.getPeriod()).append(",").append(queryColumnSql);
		return sql;
	}

	//分组条件
	private StringBuilder getGroupColumnSql(NoticeDataReq req) {
		StringBuilder sql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		StringBuilder groupColumnSql = new StringBuilder();
		if (StringUtils.isNotBlank(queryColumn)) {
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
		}
		sql.append(req.getPeriod()).append(groupColumnSql);
		return sql;
	}

	//筛选条件
	public String selectCondition(NoticeDataReq req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//主游戏
		String pgIdArr = req.getParentGameArr();
		//子游戏
		String gameIdArr = req.getGameArr();
		//区服
		String areaIdArr = req.getAreaArr();
		//主渠道
		String parentChlArr = req.getParentChannelArr();
		//子渠道
		String childChlArr = req.getChildChannelArr();
		//公告ID
		String noticeId = req.getNoticeId();

		//日期
		Long sTime = req.getStartTime();
		Long eTime = req.getFinishTime();
		if (StringUtils.isBlank(bieming)) {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {

				sqlCondition.append(" and dx_event_time  >= ").append(DateUtils.stringToStartDate(sTime + "", DateUtils.YYYYMMDD).getTime());
				sqlCondition.append(" and dx_event_time  <= ").append(DateUtils.stringToEndDate(eTime + "", DateUtils.YYYYMMDD_HHMMSS).getTime());
			}
			if (StringUtils.isNotBlank(parentChlArr)) {
				if (parentChlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentChlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentChlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(childChlArr)) {
				if (childChlArr.contains(",")) {
					sqlCondition.append(" and chl in ('" + childChlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and chl  = '" + childChlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgIdArr)) {
				if (pgIdArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgIdArr + ")");
				} else {
					sqlCondition.append(" and pgid  = " + pgIdArr);
				}
			}
			if (StringUtils.isNotBlank(gameIdArr)) {
				if (gameIdArr.contains(",")) {
					sqlCondition.append(" and gameid in (" + gameIdArr + ")");
				} else {
					sqlCondition.append(" and gameid  = " + gameIdArr);
				}
			}
			if (StringUtils.isNotBlank(areaIdArr)) {
				if (areaIdArr.contains(",")) {
					sqlCondition.append(" and areaid in ('" + areaIdArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and areaid  = '" + areaIdArr + "'");
				}
			}
			if (StringUtils.isNotBlank(noticeId)) {
				sqlCondition.append(" and notice_id  = " + noticeId);
			}
			return sqlCondition.toString();
		} else {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and ").append(bieming).append(".dx_event_time  >= ").append(DateUtils.stringToStartDate(sTime + "", DateUtils.YYYYMMDD).getTime());
				sqlCondition.append(" and ").append(bieming).append(".dx_event_time  <= ").append(DateUtils.stringToEndDate(eTime + "", DateUtils.YYYYMMDD).getTime());
			}

			if (StringUtils.isNotBlank(parentChlArr)) {
				if (parentChlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentChlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentChlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(childChlArr)) {
				if (childChlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".chl in ('" + childChlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".chl  = '" + childChlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgIdArr)) {
				if (pgIdArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgIdArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid  = " + pgIdArr);
				}
			}
			if (StringUtils.isNotBlank(gameIdArr)) {
				if (gameIdArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + gameIdArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid  = " + gameIdArr);
				}
			}
			if (StringUtils.isNotBlank(areaIdArr)) {
				if (areaIdArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".areaid in (" + areaIdArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".areaid  = " + areaIdArr);
				}
			}
			if (StringUtils.isNotBlank(noticeId)) {
				sqlCondition.append(" and ").append(bieming).append(".notice_id  = " + noticeId);
			}
			return sqlCondition.toString();
		}
	}

}
